const sqlite3 = require('sqlite3').verbose();
const moment = require('moment');

// 创建数据库连接
const db = new sqlite3.Database('./database.db');

// 初始化数据库
db.serialize(() => {
    console.log('开始初始化数据库...');
    
    // 创建卡密表
    db.run(`DROP TABLE IF EXISTS card_keys`);
    db.run(`DROP TABLE IF EXISTS usage_logs`);
    
    db.run(`CREATE TABLE IF NOT EXISTS card_keys (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        card_key TEXT UNIQUE NOT NULL,
        permission_level INTEGER DEFAULT 1,
        expires_at INTEGER NOT NULL,
        is_used BOOLEAN DEFAULT FALSE,
        used_by TEXT,
        used_at INTEGER,
        created_at INTEGER DEFAULT (strftime('%s', 'now'))
    )`);

    // 创建使用记录表
    db.run(`CREATE TABLE IF NOT EXISTS usage_logs (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        card_key TEXT NOT NULL,
        device_id TEXT NOT NULL,
        action TEXT NOT NULL,
        timestamp INTEGER DEFAULT (strftime('%s', 'now'))
    )`);

    // 插入测试数据
    const testCards = [
        { key: 'TEST-2024-001', level: 1, days: 30, price: 9.99 },
        { key: 'TEST-2024-002', level: 2, days: 90, price: 24.99 },
        { key: 'TEST-2024-003', level: 3, days: 365, price: 89.99 },
        { key: 'VIP-2024-001', level: 4, days: 30, price: 19.99 },
        { key: 'VIP-2024-002', level: 4, days: 365, price: 199.99 }
    ];

    const insertStmt = db.prepare(
        'INSERT INTO card_keys (card_key, permission_level, expires_at) VALUES (?, ?, ?)'
    );

    testCards.forEach(card => {
        const expiresAt = moment().add(card.days, 'days').unix();
        insertStmt.run(card.key, card.level, expiresAt);
        console.log(`添加卡密: ${card.key} - ${card.days}天权限 - ￥${card.price}`);
    });

    insertStmt.finalize();

    console.log('数据库初始化完成！');
    console.log(`共添加 ${testCards.length} 个测试卡密`);
});

// 查询并显示所有卡密
db.all('SELECT * FROM card_keys', (err, rows) => {
    if (err) {
        console.error('查询错误:', err);
    } else {
        console.log('\n当前卡密列表:');
        rows.forEach(row => {
            console.log(`ID: ${row.id}, 卡密: ${row.card_key}, 权限等级: ${row.permission_level}, 过期时间: ${moment.unix(row.expires_at).format('YYYY-MM-DD HH:mm:ss')}`);
        });
    }
    
    db.close((err) => {
        if (err) {
            console.error('关闭数据库时出错:', err);
        } else {
            console.log('\n数据库连接已关闭');
        }
    });
});
